﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.IO;
using System.Text;
using System.Text.RegularExpressions;
using System.Windows.Forms;
using System.Xml;
using Common.Utilities;
using Microsoft.Office.Interop.Excel;
using Application = Microsoft.Office.Interop.Excel.Application;
using DataTable = System.Data.DataTable;

namespace Workflows.Components.TextMining.Clustering
{
	/// <summary>
	/// 
	/// </summary>
	public partial class DocumentTermListEditor2 : Form
	{
		/// <summary>
		/// 
		/// </summary>
		/// <param name="xmlTermListFilePath"></param>
		public delegate void OnTermListUpdatedFromExcelFile(string xmlTermListFilePath);

		public event OnTermListUpdatedFromExcelFile TermListUpdatedFromExcelFile;

		/// <summary>
		/// 
		/// </summary>
		public DocumentTermListEditor2()
		{
			InitializeComponent();
		}

		private void btn_Browse_Click(object sender, EventArgs e)
		{
			OpenFileDialog dlg=new OpenFileDialog();
			dlg.Filter = "Excel files(*.xls;*.xlsx)|*.xls;*.xlsx|All files(*.*)|*.*";
			if(dlg.ShowDialog()==DialogResult.OK)
			{
				this.txt_ExcelFile.Text = dlg.FileName;
				List<string> colHeaderList=new List<string>();
				DataTable dtData = new DataTable();
				this.OpenExcelFile(dlg.FileName, ref colHeaderList, ref dtData);
				if(dtData !=null && dtData.Rows.Count>0)
				{
					this.FillColumnNameDropDownList(this.cbo_DocIDField, colHeaderList);
					this.FillColumnNameDropDownList(this.cbo_TermWeight, colHeaderList);
					this.FillColumnNameDropDownList(this.cbo_TermIDField, colHeaderList);
					this.FillColumnNameDropDownList(this.cbo_TermField, colHeaderList);
					this.FillColumnNameDropDownList(this.cbo_DocLabelField, colHeaderList);
					this.FillColumnNameDropDownList(this.cbo_DocContentField, colHeaderList);
				}
			}
		}

		private void FillColumnNameDropDownList(ComboBox comboBox, List<string> colHeaders)
		{
			comboBox.Items.Clear();
			foreach (string colHeader in colHeaders)
				comboBox.Items.Add(colHeader);
		}

		private void OpenExcelFile(string excelFilePath, ref List<string> colHeaderList, ref DataTable dtData)
		{
			Application excelApp = new Application();
			
			try
			{
				excelApp.Visible = false;
				excelApp.DisplayAlerts = false;
				object missing = Type.Missing;

				Workbook wk = excelApp.Workbooks.Open(excelFilePath, missing, missing, missing, missing,
				                             missing, missing, missing, missing, missing, missing, missing, missing,
				                             missing, missing);

				Worksheet sheet = wk.Worksheets[1] as Worksheet;
				dtData=new DataTable(sheet.Name);
				// determine row number that contains column header, 
				// when unable to find distinct col header within 255 rows, use 
				// automatically generated column names 
				Dictionary<string, int> colHeaderIdxMapings = new Dictionary<string, int>();
				int rowNum = 1;
				int maxColIdxThatContainsData = 1;
				int continuousEmptyRowCount = 0;
				while (rowNum <= 255)
				{
					int continuousEmptyCellCount = 0;
					Dictionary<string, int> colHeaderIdxMapingsCopy = new Dictionary<string, int>();
					int colIdx = 1;
					while (colIdx <= 255)
					{
						object cellHeaderValue = this.GetCell(sheet, colIdx, rowNum).Value2;
						if (cellHeaderValue != null && !string.IsNullOrEmpty(cellHeaderValue.ToString()))
						{
							if (colIdx > maxColIdxThatContainsData)
								maxColIdxThatContainsData = colIdx;
							string colHeader = cellHeaderValue.ToString();
							if (!colHeaderIdxMapingsCopy.ContainsKey(colHeader) && colHeaderIdxMapingsCopy.Count == colIdx - 1)
							{
								colHeaderIdxMapingsCopy.Add(colHeader, colIdx);
							}
							continuousEmptyCellCount = 0;
						}
						else
						{
							continuousEmptyCellCount++;
							if(continuousEmptyCellCount>5)
								break;
						}
						colIdx++;
					}
					if (colHeaderIdxMapingsCopy.Count > colHeaderIdxMapings.Count)
					{
						colHeaderIdxMapings = colHeaderIdxMapingsCopy;
						continuousEmptyRowCount = 0;
					}
					else if(colHeaderIdxMapingsCopy.Count==0)
					{
						continuousEmptyRowCount++;
						if (continuousEmptyRowCount > 5)
							break;
					}
					rowNum++;
				}

				if (maxColIdxThatContainsData == colHeaderIdxMapings.Count)
				{
					string[] colHeaders = new string[colHeaderIdxMapings.Count];
					colHeaderIdxMapings.Keys.CopyTo(colHeaders, 0);
					colHeaderList=new List<string>();
					colHeaderList.AddRange(colHeaders);
					int rowNumContainingColHeader = colHeaderIdxMapings[colHeaders[0]];
					foreach (string colHeader in colHeaders)
					{
						dtData.Columns.Add(colHeader, typeof(string));
					}
					int continuousEmptyRows = 0;
					for (int i = rowNumContainingColHeader + 1; i < 65535; i++)
					{
						DataRow dr = dtData.NewRow();
						StringBuilder rowContent = new StringBuilder();
						foreach (string colHeader in colHeaders)
						{
							int colIdx = colHeaderIdxMapings[colHeader];
							object cellValue = this.GetCell(sheet, colIdx, i).Value2;
							if (cellValue != null)
							{
								dr[colHeader] = cellValue.ToString();
								rowContent.Append(cellValue.ToString());
							}
						}
						if (rowContent.Length > 0)
						{
							dtData.Rows.Add(dr);
							continuousEmptyRows = 0;
						}
						else
							continuousEmptyRows++;

						if (continuousEmptyRows >= 5)
							break;
					}
				}
				else
				{
					// auto generate column name, read from row #1 
					colHeaderList = new List<string>();
					for (int i = 1; i <= maxColIdxThatContainsData; i++)
					{
						dtData.Columns.Add("Col_" + i.ToString(), typeof(string));
						colHeaderList.Add("Col_" + i.ToString());
					}
					int continuousEmptyRows = 0;
					for (int i = 1; i < 65535; i++)
					{
						DataRow dr = dtData.NewRow();
						StringBuilder rowContent = new StringBuilder();
						for (int k = 1; k <= maxColIdxThatContainsData; k++)
						{
							object cellValue = this.GetCell(sheet, k, i).Value2;
							if (cellValue != null)
							{
								dr[k] = cellValue.ToString();
								rowContent.Append(cellValue.ToString());
							}
						}
						if (rowContent.Length > 0)
						{
							dtData.Rows.Add(dr);
							continuousEmptyRows = 0;
						}
						else
							continuousEmptyRows++;

						if (continuousEmptyRows >= 5)
							break;
					}
				}
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message);
			}
			finally
			{
				if (excelApp != null)
					excelApp.Quit();
			}
		}


		#region range
		/// <summary></summary>
		public Range GetCell(Worksheet sheet, int colIndex, int rowIndex)
		{
			return (Range)sheet.Cells[rowIndex, colIndex];
		}
		/// <summary></summary>
		public Range GetRange(Worksheet sheet, string name)
		{
			return this.GetCell(sheet, this.GetColumnIndex(name), this.GetRowIndex(name));
		}
		/// <summary></summary>
		public int GetColumnIndex(string name)
		{
			Regex charRegex = new Regex("(^[^\\d]+)\\d+$");
			Match charMatch = charRegex.Match(name);
			Char[] colChars = null;
			if (charMatch.Success)
			{
				colChars = charMatch.Groups[1].Value.ToCharArray();
			}
			else
			{
				colChars = name.ToCharArray();
			}

			int colIndex = 0;
			for (int i = 0; i < colChars.Length; i++)
			{
				int j = colChars.Length - i - 1;
				int k = 26 * i;
				char x = colChars[j];
				int m = this.Asc(x) - this.Asc('A') + 1;
				colIndex += k + m;
			}
			return colIndex;
		}
		/// <summary></summary>
		public string GetColumnName(int colIndex)
		{
			char left = ' ';
			char right = ' ';
			for (int i = 0; i <= 26; i++)
			{
				for (int k = 1; k <= 26; k++)
				{
					int index = i * 26 + k;
					if (index == colIndex)
					{
						if (i > 0)
						{
							left = this.Chr(64 + i);
						}
						right = this.Chr(64 + k);

						k = 100;
						i = 100;
						break;
					}
				}
			}
			return string.Concat(left.ToString(), right.ToString()).Trim();
		}
		/// <summary></summary>
		public int GetRowIndex(string name)
		{
			Regex charRegex = new Regex("(^[^\\d]+)(\\d+)$");
			Match charMatch = charRegex.Match(name);
			int rowIndex = int.Parse(charMatch.Groups[2].Value);
			return rowIndex;
		}
		/// <summary></summary>
		public int Asc(char c)
		{
			return (int)Encoding.ASCII.GetBytes(new char[] { c })[0];
		}
		/// <summary></summary>
		public char Chr(int i)
		{
			return Convert.ToChar(i);
		}
		#endregion

		private void ck_TermWeightNotEqual_CheckedChanged(object sender, EventArgs e)
		{
			if (this.ck_TermWeightNotEqual.Checked)
				this.num_ExcludeTermWeight.Enabled = true;
			else
				this.num_ExcludeTermWeight.Enabled = false;
		}

		private void ck_TermWeightGreaterThan_CheckedChanged(object sender, EventArgs e)
		{
			if (this.ck_TermWeightGreaterThan.Checked)
				this.num_MinTermWeight.Enabled = true;
			else
				this.num_MinTermWeight.Enabled = false;
		}

		private void ck_TermWeightLessThan_CheckedChanged(object sender, EventArgs e)
		{
			if (this.ck_TermWeightLessThan.Checked)
				this.num_MaxTermWeight.Enabled = true;
			else
				this.num_MaxTermWeight.Enabled = false;
		}

		private void btn_OK_Click(object sender, EventArgs e)
		{
			if(this.cbo_DocIDField.SelectedIndex<0)
			{
				MessageBox.Show("Please select field mapping to doc id (Appl_ID)");
				this.cbo_DocIDField.Focus();
				return;
			}
			if (this.cbo_TermIDField.SelectedIndex < 0)
			{
				MessageBox.Show("Please select field mapping to term id (Concept_ID)");
				this.cbo_TermIDField.Focus();
				return;
			}
			if (this.cbo_TermField.SelectedIndex < 0)
			{
				MessageBox.Show("Please select field mapping to term (Concept)");
				this.cbo_TermField.Focus();
				return;
			}
			if (this.cbo_TermWeight.SelectedIndex < 0)
			{
				MessageBox.Show("Please select field mapping to term weight (Concept_Rank)");
				this.cbo_TermWeight.Focus();
				return;
			}

			try
			{
				// doc id -> (term id -> doctermfreq)
				Dictionary<int, Dictionary<int, DocTermFreq>> docTerms = new Dictionary<int, Dictionary<int, DocTermFreq>>();
				Dictionary<int,string> docLabels=new Dictionary<int, string>();
				Dictionary<int,string> docContents=new Dictionary<int, string>();

				List<string> colHeaderList=new List<string>();
				DataTable dtData = null;
				this.OpenExcelFile(this.txt_ExcelFile.Text, ref colHeaderList, ref dtData);
				if(dtData !=null && dtData.Rows.Count>0)
				{
					string docIDFieldName = this.cbo_DocIDField.SelectedItem.ToString();
					string termIDFieldName = this.cbo_TermIDField.SelectedItem.ToString();
					string termFieldName = this.cbo_TermField.SelectedItem.ToString();
					string termWeightFieldName = this.cbo_TermWeight.SelectedItem.ToString();
					string docLabelFieldName = this.cbo_DocLabelField.SelectedIndex < 0
					                           	? string.Empty
					                           	: this.cbo_DocLabelField.SelectedItem.ToString();
					string docContentFieldName = this.cbo_DocContentField.SelectedIndex < 0
					                             	? string.Empty
					                             	: this.cbo_DocContentField.SelectedItem.ToString();

					foreach(DataRow dr in dtData.Rows)
					{
						int docID = int.Parse(dr[docIDFieldName].ToString());
						int termID = int.Parse(dr[termIDFieldName].ToString());
						string term = (string) dr[termFieldName];
						double termWeight = double.Parse(dr[termWeightFieldName].ToString());
						string docLabel = string.Empty;
						if (!string.IsNullOrEmpty(docLabelFieldName) && dr[docLabelFieldName]!=DBNull.Value)
							docLabel = (string) dr[docLabelFieldName];
						string docContent = string.Empty;
						if (!string.IsNullOrEmpty(docContentFieldName) && dr[docContentFieldName]!=DBNull.Value)
							docContent =(string) dr[docContentFieldName];
						DocTermFreq docTermFreq=new DocTermFreq();
						docTermFreq.Count = 1;
						docTermFreq.Phrase = term;
						docTermFreq.TermID = termID;
						docTermFreq.Weight = termWeight;

						if(!docLabels.ContainsKey(docID) && !string.IsNullOrEmpty(docLabel))
						{
							docLabels.Add(docID,docLabel);
						}
						if(!docContents.ContainsKey(docID) && !string.IsNullOrEmpty(docContent))
						{
							docContents.Add(docID,docContent);
						}

						if(docTerms.ContainsKey(docID))
						{
							Dictionary<int, DocTermFreq> termsForDoc = docTerms[docID];
							if(termsForDoc.ContainsKey(termID))
							{
								DocTermFreq existingTerm = termsForDoc[termID];
								existingTerm.Count++;
								termsForDoc[termID] = existingTerm;
							}
							else
							{
								termsForDoc.Add(termID,docTermFreq);
								docTerms[docID] = termsForDoc;
							}
						}
						else
						{
							Dictionary<int, DocTermFreq> termsForDoc = new Dictionary<int, DocTermFreq>();
							termsForDoc.Add(termID,docTermFreq);
							docTerms.Add(docID,termsForDoc);
						}
					}


					XmlDocument xDoc = new XmlDocument();
					xDoc.LoadXml("<Docs></Docs>");
					XmlNode root = xDoc.DocumentElement;
					foreach (int docID in docTerms.Keys)
					{
						XmlNode docNode = XmlDataUtil.AddElement(ref xDoc, root, "Doc");
						XmlDataUtil.UpdateAttribute(ref xDoc, docNode, "DocID", docID.ToString());
						string docLabel = string.Empty;
						if (docLabels.ContainsKey(docID))
						{
							docLabel = docLabels[docID];
						}
						XmlDataUtil.UpdateAttribute(ref xDoc, docNode, "Label", docLabel);
						Dictionary<int, DocTermFreq> terms = docTerms[docID];
						foreach (int termID in terms.Keys)
						{
							DocTermFreq docTermFreq = terms[termID];
							
							{
								XmlNode termNode = XmlDataUtil.AddElement(ref xDoc, docNode, "Term");

								XmlDataUtil.UpdateAttribute(ref xDoc, termNode, "TermID", termID.ToString());
								XmlDataUtil.UpdateAttribute(ref xDoc, termNode, "Phrase", docTermFreq.Phrase);
								XmlDataUtil.UpdateAttribute(ref xDoc, termNode, "Count", docTermFreq.Count.ToString());
								XmlDataUtil.UpdateAttribute(ref xDoc, termNode, "Weight", docTermFreq.Weight.ToString());
							}
						}
						if (docContents.ContainsKey(docID))
						{
							string docContent = docContents[docID];
							XmlDataUtil.AddCdataElement(ref xDoc, docNode, "Content", docContent);
						}
					}
					string xmlTermListFilePath = Path.Combine(Path.GetDirectoryName(this.txt_ExcelFile.Text),
					                                          Path.GetFileNameWithoutExtension(this.txt_ExcelFile.Text) +
					                                          "_TermList.xml");
					xDoc.Save(xmlTermListFilePath);

					if (this.TermListUpdatedFromExcelFile != null)
						this.TermListUpdatedFromExcelFile(xmlTermListFilePath);

					this.Close();
				}
			}
			catch (Exception ex)
			{
				MessageBox.Show(ex.Message);
			}
		}

		private void btn_Cancel_Click(object sender, EventArgs e)
		{
			this.Close();
		}
	}
}
